package org.jetnuke.dao.generic;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import net.jforum.JForumExecutionContext;
import net.jforum.dao.generic.AutoKeys;
import net.jforum.util.preferences.SystemGlobals;

import org.jetnuke.dao.PictureDAO;
import org.jetnuke.entities.Picture;

public class GenericPictureDAO extends AutoKeys implements PictureDAO {

	public Picture retrieveByPK(int id) throws Exception{
		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PictureModel.selectByID"));
		p.setInt(1, id);
		
		ResultSet rs = p.executeQuery();
		Picture  s = new Picture();
		
		rs.next() ;
		fillPictureFromResultSet(s, rs);
		
		
		rs.close();
		p.close();

		return s;
	}

	protected void fillPictureFromResultSet(Picture s, ResultSet rs)throws Exception {
		s.setAID(rs.getInt("AID"));
		s.setAPPROVED(rs.getString("APPROVED"));
		s.setCAPTION(rs.getString("CAPTION"));
		s.setFILENAME(rs.getString("FILENAME"));
		s.setFILEPATH(rs.getString("FILEPATH"));
		s.setFILESIZE(rs.getInt("FILESIZE"));
		s.setHITS(rs.getInt("HITS"));
		s.setKEYWORDS(rs.getString("KEYWORDS"));
		s.setMEDIUMMIMETYPE(rs.getString("MEDIUMMIMETYPE"));
		s.setMEDIUMNAME(rs.getString("MEDIUMNAME"));
		s.setMIMETYPE(rs.getString("MIMETYPE"));
		s.setMTIME(rs.getTimestamp("MTIME"));
		s.setPHEIGHT(rs.getInt("PHEIGHT"));
		s.setPIC_RATING(rs.getInt("PIC_RATING"));
		s.setPIC_RAW_IP(rs.getString("PIC_RAW_IP"));
		s.setPWIDTH(rs.getInt("PWIDTH"));
		s.setTITLE(rs.getString("TITLE"));
		s.setTOTAL_SIZE(rs.getInt("TOTAL_FILESIZE"));
		s.setTHUMBMIMETYPE(rs.getString("THUMBMIMETYPE"));
		s.setTHUMBNAME(rs.getString("THUMBNAME"));
		s.setUID(rs.getInt("UID"));
		s.setVOTES(rs.getInt("VOTES"));
		s.setPID(rs.getInt("PID"));
		
	}

	public int addNew(Picture picture) throws Exception {
		PreparedStatement p = this.getStatementForAutoKeys("PictureModel.addNew");
		this.setAutoGeneratedKeysQuery(SystemGlobals.getSql("PictureModel.lastGeneratedPictureID"));
		initPicture(picture, p);
		
		int id = this.executeAutoKeysQuery(p);
		p.close();		
		return id;
	}

	private void initPicture(Picture picture, PreparedStatement p)throws Exception {
		p.setInt(1,picture.getAID());
		p.setString(2,picture.getAPPROVED());
		p.setString(3,picture.getCAPTION());
		p.setString(4,picture.getFILENAME());
		p.setString(5,picture.getFILEPATH());
		p.setInt(6,picture.getFILESIZE());
		p.setInt(7,picture.getHITS());
		p.setString(8,picture.getKEYWORDS());
		p.setTimestamp(9,new Timestamp(picture.getMTIME().getTime()));
		p.setInt(10,picture.getPHEIGHT());
		p.setInt(11,picture.getPIC_RATING());
		p.setString(12,picture.getPIC_RAW_IP());
		p.setInt(13,picture.getPWIDTH());
		p.setString(14,picture.getTITLE());
		p.setInt(15,picture.getTOTAL_SIZE());
		p.setInt(16, picture.getUID());
		p.setInt(17,picture.getVOTES());
		p.setString(18, picture.getMIMETYPE());
		p.setString(19,picture.getTHUMBNAME());
		p.setString(20,picture.getTHUMBMIMETYPE());
		p.setString(21,picture.getMEDIUMNAME());
		p.setString(22,picture.getMEDIUMMIMETYPE());
		
	}

	public List retrieveByUser(int id) throws Exception {
		PreparedStatement p= JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PictureModel.selectByUser"));
		p.setInt(1,id);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		
		return list;
	}

	protected List processSelectAll(ResultSet rs)throws Exception {
		List list = new ArrayList();
		while (rs.next()) {
			Picture u = new Picture();
			fillPictureFromResultSet(u,rs);			
			list.add(u);
		}
		return list;
	}

	public void update(Picture picture) throws Exception {
		PreparedStatement p= JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PictureModel.update"));
		p.setInt(1,picture.getAID());
		p.setString(2,picture.getAPPROVED());
		p.setString(3,picture.getCAPTION());
		p.setString(4,picture.getFILENAME());
		p.setString(5,picture.getFILEPATH());
		p.setInt(6,picture.getFILESIZE());
		p.setInt(7,picture.getHITS());
		p.setString(8,picture.getKEYWORDS());
		p.setTimestamp(9,new Timestamp(picture.getMTIME().getTime()));
		p.setInt(10,picture.getPHEIGHT());
		p.setInt(11,picture.getPIC_RATING());
		p.setString(12,picture.getPIC_RAW_IP());
		p.setInt(13,picture.getPWIDTH());
		p.setString(14,picture.getTITLE());
		p.setInt(15,picture.getTOTAL_SIZE());
		p.setInt(16, picture.getUID());
		p.setInt(17,picture.getVOTES());
		p.setString(18, picture.getMIMETYPE());
		p.setString(19,picture.getTHUMBNAME());
		p.setString(20,picture.getTHUMBMIMETYPE());
		p.setString(21,picture.getMEDIUMNAME());
		p.setString(22,picture.getMEDIUMMIMETYPE());
		p.setInt(23,picture.getPID());
		p.executeUpdate();
		p.close();
		
	}

	public List getLast(int last) throws Exception {
		PreparedStatement p= JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PictureModel.selectLast"));
		p.setInt(1,last);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		
		return list;
	}

	public void moveAlbum(int userid, int init, int final_) throws Exception {
		PreparedStatement p= JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PictureModel.moveAlbum"));
		p.setInt(1, final_);
		p.setInt(2, userid);
		p.setInt(3, init);
		p.executeUpdate();
		p.close();
		
	}

	public List selectByAlbum(int aid) throws Exception {
		PreparedStatement p= JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PictureModel.selectByAlbum"));
		p.setInt(1,aid);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		
		return list;
	}

	public int getLastByAlbum(int aid) throws Exception {
		PreparedStatement p= JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PictureModel.getLastByAlbum"));
		p.setInt(1,aid);
		ResultSet rs = p.executeQuery();
		int ret=0;
		if(rs.next()){
			
			ret=rs.getInt(1);
			if(rs.wasNull())
				ret=0;
		}
		rs.close();
		p.close();
		return ret;
	}

	public List search(String searchval, int start, int limit) throws Exception {
		PreparedStatement p= JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PictureModel.search"));
		String val="%"+searchval+"%";
		p.setString(1,val);
		p.setString(2,val);
		p.setString(3,val);
		p.setString(4,val);
		p.setInt(5,start);
		p.setInt(6,limit);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		
		return list;
	}

	public int countAllSearch(String searchval) throws Exception {
		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PictureModel.countAllSearch"));
		String val="%"+searchval+"%";
		p.setString(1,val);
		p.setString(2,val);
		p.setString(3,val);
		p.setString(4,val);
		ResultSet rs=p.executeQuery();
		rs.next();
		int ret=rs.getInt(1);
		rs.close();
		p.close();
		return ret;
	}

	public void vote(int pid, int score) throws Exception {
		PreparedStatement p= JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PictureModel.vote"));
		p.setInt(1,score);
		p.setInt(2,pid);
		p.executeUpdate();
		p.close();
		
	}

	public List getTop(int limit) throws Exception {
		PreparedStatement p= JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PictureModel.selectTopRated"));
		p.setInt(1,limit);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		
		return list;
	}

	public void deleteByPID(int pid) throws Exception {
		PreparedStatement p= JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("PictureModel.deleteByPID"));
		p.setInt(1,pid);
		p.executeUpdate();
		p.close();
		
	}


}
